package cjgl.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import cjgl.entity.Course;
import cjgl.entity.Grade;

public class GradeDAO {

		public Grade findById(Integer id) {
			Grade gra = null;
			String sql="select * from grade t where t.id="+id;
			Connection conn = ConnectionFactory.getConnection();
			if(conn!=null) {
			Statement stmt;
			try {
				stmt = conn.createStatement();
				ResultSet rs = stmt.executeQuery(sql);
				if(rs.next()){
					gra = new Grade(rs.getInt(1), rs.getInt(2), rs.getInt(3), rs.getInt(4));
				}
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			}
			return gra;
		}
		
		
		public List<Grade> findAll(){
			List<Grade> list = new ArrayList<Grade>();
			String sql = "select * from grade";
			Connection conn = ConnectionFactory.getConnection();
			if(conn!=null) {
			try {
				Statement stmt = conn.createStatement();
				ResultSet rs = stmt.executeQuery(sql);
				while(rs.next()) {
					Grade gra=new Grade(rs.getInt(1), rs.getInt(2), rs.getInt(3), rs.getInt(4));
					list.add(gra);
				}
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			}
			return list;
			
		}
		
		public synchronized void insert(Grade grade) {
			String sql="insert into grade values(null,?,?,?)";
			Connection conn = ConnectionFactory.getConnection();
			if(conn!=null) {
			PreparedStatement pstmt;
			try {
				pstmt = conn.prepareStatement(sql);
				pstmt.setInt(1,grade.getStudent().getId());
				pstmt.setInt(2,grade.getCourse().getId());
				if(grade.getGrade()==null) {
					pstmt.setNull(3,java.sql.Types.INTEGER);
				}else{
					pstmt.setInt(3,grade.getGrade());
				}
				pstmt.execute();
				sql="select * from grade t order by t.id desc";
				ResultSet rs = pstmt.executeQuery(sql);
				rs.next();
				grade.setId(rs.getInt(1));
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			}
		}
		
		public void update(Grade grade) {
			String sql = "update grade t set t.studentId=?,t.courseId=?,t.grade=? where t.id=?";
			Connection conn = ConnectionFactory.getConnection();
			if(conn!=null) {
				PreparedStatement pstmt;
				try {
					pstmt = conn.prepareStatement(sql);
					pstmt.setInt(1,grade.getStudent().getId());
					pstmt.setInt(2,grade.getCourse().getId());
					pstmt.setInt(3,grade.getGrade());
					pstmt.setInt(4,grade.getId());
					pstmt.execute();
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
				
			}
		}
		
		
		public void delete(Integer id) {
			String sql="delete from grade Where id="+id;
			Connection conn = ConnectionFactory.getConnection();
			if(conn!=null) {
				Statement stmt;
				try {
					stmt = conn.createStatement();
					stmt.execute(sql);
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		
		public static void main(String[] args) {
			GradeDAO grade= new GradeDAO();
			Grade zc= new Grade(null,3,1,100);
			grade.insert(zc);;
			List<Grade> list= grade.findAll();
			for(Grade c :list) {
				System.out.println(c);
			}
		}


		public List<Grade> findByStuId(Integer stuId) {
			List<Grade> list = new ArrayList<Grade>();
			String sql ="select * from grade where studentId=?";
			Connection conn = ConnectionFactory.getConnection();
			if(conn!=null) {
				PreparedStatement pstmt;
				try {
					pstmt = conn.prepareStatement(sql);
					pstmt.setInt(1, stuId);
					ResultSet rs = pstmt.executeQuery();
					while(rs.next()) {
						Grade gra =new Grade(rs.getInt(1),rs.getInt(2),rs.getInt(3),rs.getInt(4));
						list.add(gra);
					}
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			return list;
		}


		public Grade findByStuIdAndKcId(Integer id, Integer kcId) {
			Grade gra = null;
			String sql="select * from grade where studentId=? and courseId=?";
			Connection conn = ConnectionFactory.getConnection();
			if(conn!=null) {
			PreparedStatement pstmt;
			try {
				pstmt = conn.prepareStatement(sql);
				pstmt.setInt(1, id);
				pstmt.setInt(2, kcId);
				ResultSet rs = pstmt.executeQuery();
				if(rs.next()) {
					gra = new Grade(rs.getInt(1), rs.getInt(2),rs.getInt(3), rs.getInt(4));
				}
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			}
			return gra;
		}


		public List<Course> findCourseByBjId(Integer bjId) {
			List<Course> courses = new ArrayList<Course>();
			String sql="SELECT * FROM course c WHERE c.id in( SELECT DISTINCT g.courseId FROM grade g LEFT JOIN student s ON g.studentId=s.id WHERE tbClassId=?)";
			Connection conn = ConnectionFactory.getConnection();
			if(conn!=null) {
				PreparedStatement pstmt;
				try {
					pstmt = conn.prepareStatement(sql);
					pstmt.setInt(1, bjId);
					ResultSet rs = pstmt.executeQuery();
					while(rs.next()) {
						Course course = new Course(rs.getInt(1), rs.getString(2), rs.getString(3));
						courses.add(course);
					}
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			return courses;
		}


		public List<List<Object>> getCjb(Integer bjId, List<Course> courses) {
			List<List<Object>> cjb = new ArrayList<List<Object>>();
			String sql ="SELECT s.stuNo,s.`name`,";
			for (Course course : courses) {
				sql+="SUM(CASE WHEN c.id="+course.getId()+" THEN g.grade ELSE 0 END) "+course.getName()+ ",";
			}
			sql+="SUM(g.grade) zongfen " + 
					"FROM grade g " + 
					"LEFT JOIN student s ON g.studentId=s.id " + 
					"LEFT JOIN course c ON g.courseId=c.id " + 
					"WHERE s.tbClassId=? " + 
					" GROUP BY s.id " + 
					"ORDER BY stuNo asc";
			Connection conn = ConnectionFactory.getConnection();
			if(conn!=null) {
				PreparedStatement pstmt;
				try {
					pstmt = conn.prepareStatement(sql);
					pstmt.setInt(1, bjId);
					ResultSet rs = pstmt.executeQuery();
					while(rs.next()) {
						List<Object> row = new ArrayList<Object>();
						row.add(rs.getString(1));
						row.add(rs.getString(2));
						for (Course course : courses) {
							row.add(rs.getInt(course.getName()));
						}
						row.add(rs.getInt("zongfen"));
						cjb.add(row);
					}
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			return cjb;
		}
}


